use wiki_db;


INSERT INTO Author_Table (Author_ID, Name) 
VALUES ('MURAKAMI', 'Murakami, Haruki');
INSERT INTO Author_Table (Author_ID, Name) 
VALUES ('ROWLINGJ', 'Rowling, Joanna');
INSERT INTO Author_Table (Author_ID, Name) 
VALUES ('CARSONAN', 'Carson, Anne');
INSERT INTO Author_Table (Author_ID, Name) 
VALUES ('MORRISON', 'Morrison, Toni');
INSERT INTO Author_Table (Author_ID, Name) 
VALUES ('BABELISA', 'Babel, Isaak');
INSERT INTO Author_Table (Author_ID, Name) 
VALUES ('BULGAKOW', 'Bulgakow, Michael');

Select * from Author_View;

INSERT INTO Book_Table (ISBN_13, Main_Author_ID, Title, Price) 
VALUES ('9783442730506', 'MURAKAMI', 'Naokos Lächeln', 26.00);
INSERT INTO Book_Table (ISBN_13, Main_Author_ID, Title, Price) 
VALUES ('9783551354051', 'ROWLINGJ', 'Harry Potter & Orden des Phoenix', 16.00);
INSERT INTO Book_Table (ISBN_13, Main_Author_ID, Title, Price) 
VALUES ('9783103972795', 'CARSONAN', 'Rot', 24.00);
INSERT INTO Book_Table (ISBN_13, Main_Author_ID, Title, Price) 
VALUES ('9783498005092', 'MORRISON', 'Beloved', 28.00);
INSERT INTO Book_Table (ISBN_13, Main_Author_ID, Title, Price) 
VALUES ('9783921592847', 'BABELISA', 'Die Reiterarmee', 26.00);
INSERT INTO Book_Table (ISBN_13, Main_Author_ID, Title, Price) 
VALUES ('9783630620930', 'BULGAKOW', 'Der Meister und Margarita', 14.00);

Select * from Book_View;

INSERT INTO Specimen_Table (Book_ID, ISBN_13, Shelf_Position)
VALUES ('MURA0702', '9783442730506', 'A27 B5 L6');
INSERT INTO Specimen_Table (Book_ID, ISBN_13, Shelf_Position)
VALUES ('ROWL1203', '9783551354051', 'A22 C1 L4');
INSERT INTO Specimen_Table (Book_ID, ISBN_13, Shelf_Position)
VALUES ('CARS0302', '9783103972795', 'B12 V6 L2');
INSERT INTO Specimen_Table (Book_ID, ISBN_13, Shelf_Position)
VALUES ('MORR0201', '9783498005092', 'C22 X2 L1');
INSERT INTO Specimen_Table (Book_ID, ISBN_13, Shelf_Position)
VALUES ('BABE0201', '9783921592847', 'D33 Q1 L9');
INSERT INTO Specimen_Table (Book_ID, ISBN_13, Shelf_Position)
VALUES ('BULG0304', '9783630620930', 'E12 P9 L7');

Select * from Specimen_View;

INSERT INTO Borrower_Table (Card_ID, Card_Issue_Date, Name, Address)
VALUES ('ARTJUL01', '2001-06-02', 'Artaxerxes, Julia', '88202 Sipplingen, Marktplatz 7');
INSERT INTO Borrower_Table (Card_ID, Card_Issue_Date, Name, Address)
VALUES ('ATTHEI01', '2012-02-23', 'Attila, Heinrich', '85854 Markdorf, Ravensburger Strasse 22');
INSERT INTO Borrower_Table (Card_ID, Card_Issue_Date, Name, Address)
VALUES ('PFLDES02', '2020-03-03', 'Pfleiderer, Desdemona', '87654 Niederstotzingen, Am Weiher 12');
INSERT INTO Borrower_Table (Card_ID, Card_Issue_Date, Name, Address)
VALUES ('ZEIPAT01', '2022-11-11', 'Zeisig, Patrokles', '88888 Achtburg, Burgweg 3');

Select * from Borrower_View;

INSERT INTO Borrow_Table (Card_ID, Book_ID)
VALUES ('ARTJUL01', 'MURA0702');
INSERT INTO Borrow_Table (Card_ID, Book_ID)
VALUES ('ARTJUL01', 'ROWL1203');
INSERT INTO Borrow_Table (Card_ID, Book_ID)
VALUES ('ATTHEI01', 'CARS0302');
INSERT INTO Borrow_Table (Card_ID, Book_ID)
VALUES ('ATTHEI01', 'MORR0201');
INSERT INTO Borrow_Table (Card_ID, Book_ID)
VALUES ('PFLDES02', 'BABE0201');
INSERT INTO Borrow_Table (Card_ID, Book_ID)
VALUES ('ZEIPAT01', 'BULG0304');

Select * from Borrow_View;

INSERT INTO Borrow_Event_Table (Card_ID, Book_ID, Borrow_Date, Planned_Return_Date, Real_Return_Date)
VALUES ('ARTJUL01', 'MURA0702', '2025-07-15', '2025-08-05', NULL);
INSERT INTO Borrow_Event_Table (Card_ID, Book_ID, Borrow_Date, Planned_Return_Date, Real_Return_Date)
VALUES ('ARTJUL01', 'ROWL1203', '2025-06-06', '2025-06-27', NULL);
INSERT INTO Borrow_Event_Table (Card_ID, Book_ID, Borrow_Date, Planned_Return_Date, Real_Return_Date)
VALUES ('ATTHEI01', 'CARS0302', '2025-07-18', '2025-08-08', NULL);
INSERT INTO Borrow_Event_Table (Card_ID, Book_ID, Borrow_Date, Planned_Return_Date, Real_Return_Date)
VALUES ('ATTHEI01', 'MORR0201', '2025-07-03', '2025-07-24', NULL);
INSERT INTO Borrow_Event_Table (Card_ID, Book_ID, Borrow_Date, Planned_Return_Date, Real_Return_Date)
VALUES ('PFLDES02', 'BABE0201', '2025-07-09', '2025-07-30', NULL);
INSERT INTO Borrow_Event_Table (Card_ID, Book_ID, Borrow_Date, Planned_Return_Date, Real_Return_Date)
VALUES ('ZEIPAT01', 'BULG0304', '2025-05-27', '2025-06-17', NULL);

Select * from Borrow_Event_View;

WITH cte AS
(SELECT Card_ID, Book_ID, Borrow_Date, Planned_Return_Date, DATEDIFF('2025-08-15', Planned_Return_Date) DIV 7 AS Weeks_Overdue
   FROM Borrow_Event_Table
  WHERE Real_Return_Date IS NULL)
SELECT Card_ID, Book_ID, Borrow_Date, Planned_Return_Date, Weeks_Overdue
  FROM cte
 WHERE Weeks_Overdue > 0
 ORDER BY Card_ID, Book_ID;

WITH cte AS
(SELECT s.Book_ID, b.Main_Author_ID, b.Title, b.Price
   FROM Specimen_Table s INNER JOIN Book_Table b
     ON s.ISBN_13 = b.ISBN_13
  WHERE s.Book_ID = 'BULG0304')
SELECT cte.Book_ID, a.Name, cte.Title, cte.Price
  FROM cte INNER JOIN Author_Table a
    ON cte.Main_Author_ID = a.Author_ID;